import 'dart:async';

import 'package:any_reader/public.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = new DatabaseHelper.internal();

  factory DatabaseHelper() => _instance;

  static Database _db;
  static String packName = "AnyReader";

  DatabaseHelper.internal();

  Future<Database> get db async {
    if (_db != null) {
      return _db;
    }
    _db = await initDb();

    return _db;
  }

  Future<String> _getDatabasesPath() async {
    String realPath = "";
    if (Platform.isAndroid) {
      String path = (await getExternalStorageDirectory()).path;
      realPath = path.substring(0, path.indexOf('/Android'));
      realPath = realPath + '/AnyReader/';
    } else if (Platform.isIOS) {
      realPath = (await getApplicationSupportDirectory()).path + '/AnyReader/';
    } else {
      realPath = await getDatabasesPath();
    }
    var directory = Directory(realPath);
    try {
      bool exists = await directory.exists();
      if (!exists) {
        await directory.create();
      }
    } catch (e) {
      print(e);
    }
    return realPath;
  }

  //删除数据库
  Future<void> clearDatabase({String dbName = 'db.db'}) async {
    String databasesPath = await _getDatabasesPath();
    String path = join(databasesPath, dbName);
    return await deleteDatabase(path);
  }

  //打开数据库
  Future<Database> initDb({String dbName = 'db.db', int version = 1}) async {
    String databasesPath = await _getDatabasesPath();
    String path = join(databasesPath, dbName);
    Database db =
        await openDatabase(path, version: version, onCreate: _onCreate);
    return db;
  }

  void _onCreate(Database db, int newVersion) async {
    //初始化Database操作
    //书架
    /***
     * id 自增id
     * book_id 书id
     * book_name 书名
     * author 作者
     * description 简介
     * base_url 来源域名
     * origin_url 小说源地址
     * book_type 小说类型
     * */

    await db.execute('''
      CREATE TABLE book (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT  ,
        `book_id` varchar(255)  ,
        `book_name` varchar(255)  ,
        `author` varchar(255) ,
        `description` varchar(255)  ,
        `img_url` varchar(255)  ,
        `base_url` varchar(255)  ,
        `origin_url` varchar(255)  ,
        `url` varchar(255)  ,
        `update_time` varchar(255)  ,
        `book_type` varchar(255)  ,
        `book_status` varchar(255)  ,
        `reptile_type` varchar(255),
        `total` varchar(255)  ,
        `now` varchar(255)  ,
        `position` varchar(255)  ,
        `last_opend` varchar(255)  
      )
      ''');
    await db.execute('''
      CREATE TABLE chapter (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT  ,
        `book_id` varchar(255)  ,
        `catalog_id` varchar(255) UNIQUE ,
        `num` varchar(255)  ,
        `reptile_type` varchar(255),
        `name` varchar(255)  ,
        `type` varchar(255)  ,
        `reptile_address` varchar(255)  ,
        `local` INTEGER  
      )
      ''');

    await db.execute('''
      CREATE TABLE rules (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT  ,
        `name` varchar(255)   ,
        `reptile_type_id` varchar(255)    ,
        `reason` varchar(255)
      )
      ''');

    await db.execute('''
      CREATE TABLE article (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT  ,
        `book_id` varchar(255)  ,
        `catalog_id` varchar(255)  ,
        `name` varchar(255)  ,
        `num` varchar(255)  ,
        `type` varchar(255)  ,
        `update_time` varchar(255)  ,
        `is_reptile_tool` varchar(255)  ,
        `reptile_address` varchar(255)  ,
        `content` varchar(255)  
      )
      ''');
  }

  Future<List<Book>> getAllBook() async {
    Database dbClient = await db;
    List<Book> _list = [];
    List result = await dbClient.query(
      'book',
      orderBy: "(last_opend+0) DESC",
    );
    if (result.length > 0) {
      for (int index = 0; index < result.length; index++) {
        var _item = result[index];
        Book book = Book.fromJson(
            bookId: _item['book_id'],
            title: _item['book_name'],
            author: _item['author'],
            description: _item['description'],
            imgUrl: _item['img_url'],
            baseUrl: _item['base_url'],
            originUrl: _item['origin_url'],
            url: _item['url'] ?? "",
            updateTime: _item['update_time'],
            bookType: _item['book_type'],
            bookStatus: _item['book_status'],
            reptileType: _item['reptile_type'],
            total: _item['total'],
            now: _item['now'],
            position: _item['position']);
        _list.add(book);
      }
    }
    return _list;
  }

  Future<void> deleteBook(String bookId) async {
    Database dbClient = await db;
    dbClient.transaction((_) async {
      await _.delete('book', where: "book_id=?", whereArgs: [bookId]);
      await _.delete('chapter', where: 'book_id=?', whereArgs: [bookId]);
      await _.delete('article', where: 'book_id=?', whereArgs: [bookId]);
    });
  }

  Future<void> updateBookLastOpen(String bookId) async {
    Database dbClient = await db;
    dbClient.update('book',
        {"last_opend": (new DateTime.now().millisecondsSinceEpoch).toString()},
        where: "book_id=?", whereArgs: [bookId]);
  }

  Future<bool> bookExist(String bookId, String reptileType) async {
    Database dbClient = await db;
    List result = await dbClient.query('book',
        where: "book_id=? and reptile_type=?",
        whereArgs: [bookId.toString(), reptileType.toString()]);
    return result.length == 0 ? false : true;
  }

  Future<Book> findBook(String bookId) async {
    Database dbClient = await db;
    List<Map<String, dynamic>> result = await dbClient
        .query('book', where: "book_id=?", whereArgs: [bookId.toString()]);
    Book book = new Book();
    if (result.length > 0) {
      var _item = result[0];
      book = Book.fromJson(
          bookId: _item['book_id'],
          title: _item['book_name'],
          author: _item['author'],
          description: _item['description'],
          imgUrl: _item['img_url'],
          baseUrl: _item['base_url'],
          originUrl: _item['origin_url'],
          url: _item['url'] ?? "",
          updateTime: _item['update_time'],
          bookType: _item['book_type'],
          bookStatus: _item['book_status'],
          reptileType: _item['reptile_type'],
          total: _item['total'],
          now: _item['now'],
          position: _item['position']);
    }
    return book;
  }

  Future<bool> insertBook({
    String bookId = "",
    String bookName = "",
    String author = "",
    String description = "",
    String imgUrl = "",
    String baseUrl = "",
    String originUrl = "",
    String url = "",
    String updateTime = "",
    String bookType = "",
    String bookStatus = "",
    String reptileType = "",
    int total = 0,
    int now = 0,
    int position = 0,
  }) async {
    Database dbClient = await db;
    Map<String, dynamic> insertObj = {
      "book_id": bookId,
      "book_name": bookName,
      "author": author,
      "description": description,
      "img_url": imgUrl,
      "base_url": baseUrl,
      "origin_url": originUrl,
      "url": url,
      "update_time": updateTime,
      "book_type": bookType,
      "book_status": bookStatus,
      "reptile_type": reptileType,
      "total": total,
      "now": now,
      "position": position,
    };

    int result = await dbClient.insert(
      'book',
      insertObj,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    return result > 0 ? true : false;
  }

  Future<void> updateBook({update, where, whereArgs}) async {
    Database dbClient = await db;
    dbClient.update('book', update, where: where, whereArgs: whereArgs);
  }

  Future<List<Rule>> getAllRule() async {
    Database dbClient = await db;
    List<Map<String, dynamic>> result = await dbClient.query(
      'rules', //查询的表名
      orderBy: "id ASC",
    );
    List<Rule> list = Rule.listFromDatabase(result);
    return list;
  }

  Future<void> insertRules(List<Rule> list) async {
    Database dbClient = await db;
    for (int i = 0; i < list.length; i++) {
      Rule item = list[i];
      dbClient.insert(
        'rules',
        {
          "name": item.name,
          "reptile_type_id": item.reptileTypeId,
          "reason": item.reason,
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
  }

  Future<void> insertChapter(
      String bookId, String reptileType, List<Chapter> list) async {
    Database dbClient = await db;
    var batch = dbClient.batch();
    var now = new DateTime.now();
    print("开始插入");
    for (int i = 0; i < list.length; i++) {
      Chapter item = list[i];
      Map<String, dynamic> insertObj = {
        "book_id": bookId,
        "catalog_id": item.catalogId,
        "num": item.num,
        "reptile_type": reptileType,
        "name": item.name,
        "type": item.type,
        "reptile_address": item.reptileAddress,
        "local": 1
      };
      batch.insert(
        'chapter',
        insertObj,
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
    batch.commit(noResult: true);
    var old = new DateTime.now();
    var difference = old.difference(now);
    print(difference);
    print("结束插入 耗时： time: $difference");
  }

  Future<Chapter> findChapter(String bookId, String offset) async {
    Database dbClient = await db;
    Chapter _chapter;
    List<Map<String, dynamic>> result = await dbClient.query(
      'chapter',
      where: "book_id=?",
      whereArgs: [bookId],
      limit: 1,
      offset: int.parse(offset),
    );
    if (result.length > 0) {
      _chapter = Chapter.listFromDatabase(result)[0];
    }
    return _chapter;
  }

  Future<List<Chapter>> getAllChapter(String bookId) async {
    Database dbClient = await db;
    print("读取数据库章节开始 ");
    List<Map<String, dynamic>> result = await dbClient.query(
      'chapter', //查询的表名
      where: "book_id=? ",
      whereArgs: [bookId],
      orderBy: "id ASC",
    );
    print("读取数据库章节结束");
    List<Chapter> list = Chapter.listFromDatabase(result);
    return list;
  }

  void insertArticle(
    String bookId,
    String catalogId,
    String name,
    String num,
    String type,
    String updateTime,
    String isReptileTool,
    String reptileAddress,
    String content,
  ) async {
    Database dbClient = await db;
    Map<String, dynamic> insertObj = {
      "book_id": bookId,
      "catalog_id": catalogId,
      "name": name,
      "num": num,
      "type": type,
      "update_time": updateTime,
      "is_reptile_tool": isReptileTool,
      "reptile_address": reptileAddress,
      "content": content,
    };
    dbClient.insert(
      'article',
      insertObj,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<Article> findArticle(String bookId, String catalogId) async {
    Article article;
    Database dbClient = await db;
    List<Map<String, dynamic>> result = await dbClient.query('article',
        where: "book_id=? and catalog_id=?",
        whereArgs: [bookId.toString(), catalogId.toString()]);
    if (result.length > 0) {
      String content = result[0]['content'];
      var _item = result[0];
      article = new Article(
        bookId: _item['book_id'],
        catalogId: _item['catalog_id'],
        name: _item['name'],
        num: _item['num'],
        type: _item['type'],
        updateTime: _item['update_time'],
        isReptileTool: _item['is_reptile_tool'],
        reptileAddress: _item['reptile_address'],
        content: content,
      );
    }
    return article;
  }

  Future close() async {
    var dbClient = await db;
    return dbClient.close();
  }
}
